package com.atguigu.gmallsuger.mapper;

import com.atguigu.gmallsuger.bean.VisitorStats;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface VisitorStatsMapper {
 
    
    // 安装省份分组聚合
    @Select(
        "select " +
            "   toHour(stt) hr," +
          //  "   sum(if(is_new='1', uv_ct, 0)) new_uv," +// 计算是新用户的
            "   sum(uv_ct) uv_ct, " +
            "   sum(pv_ct) pv_ct, " +
            "   sum(sv_ct) sv_ct, " +
            "   sum(uj_ct) uj_ct " +
            "from visitor_stats_2021 " +
            "where toYYYYMMDD(stt)=#{date} " +
            "group by toHour(stt) "
    )
    List<VisitorStats> getVisitorStatsByHour(int date);
    
    
    @Select(
        "select " +
            " is_new, " +
            " sum(uv_ct) uv_ct, " +
            " sum(sv_ct) sv_ct, " +
            " sum(pv_ct) pv_ct, " +
            " sum(uj_ct) uj_ct " +
            "from visitor_stats_2021 " +
            "where toYYYYMMDD(stt)=#{date} " +
            "group by is_new "
    )
    List<VisitorStats> getVisitorStatsByIsNew(int date);
    
    
    
    
    
}
